﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Library;

namespace QLKH.PopUp.taodulieu
{
    public partial class UpdateData : UserControl
    {
        private SqlConnection sqlcon;
        private SqlCommand sqlcmd;
        public string tbSudung = "";
        public static string tbSudungnew = "";
        public string tbSudungold = "";
        public static string thangnamload = "";
   
    
        DataView view = new DataView();
        public UpdateData()
        {
            InitializeComponent();
        }

        int currentPageIndex = 1;
        int pageSize = 22;
        int pageNumber = 1;
        int fistRow, lastRow;
        int rows;

        private void binCombo(ComboBox cb, DataTable dt, string name, string value, string option)
        {
            List<trangthai> list = new List<trangthai>();
            trangthai ls = null;
            ls = new trangthai();
            ls.name = option;
            ls.value = "0";
            list.Add(ls);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ls = new trangthai();
                    ls.name = dt.Rows[i][name].ToString();
                    ls.value = dt.Rows[i][value].ToString();
                    list.Add(ls);
                }
            }
            cb.DataSource = list;
            cb.DisplayMember = "name";
            cb.ValueMember = "value";
        }


        private void UpdateData_Load(object sender, EventArgs e)
        {
            //Int16 idDHtong = Int16.Parse(cbbDHT.SelectedValue.ToString());
            //Int16 idPhuongx = Int16.Parse(cbbPhuong.SelectedValue.ToString());
            //Int16 idP = Int16.Parse(cbbPho.SelectedValue.ToString());

            //load gợi ý DHT
            clDHTong cldht = new clDHTong();
            AutoCompleteStringCollection dsdht = new AutoCompleteStringCollection();

            DataTable dtds = cldht.getdata(1);
            if (dtds != null && dtds.Rows.Count > 0)
            {
                foreach (DataRow row in dtds.Rows)
                {
                    dsdht.Add(row["tenDHTong"].ToString());
                }
            }

            cbbDHT.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            cbbDHT.AutoCompleteSource = AutoCompleteSource.CustomSource;
            cbbDHT.AutoCompleteCustomSource = dsdht;
            //end gợi ý DHT

            //load gợi ý phường xã
            clPX clpx = new clPX();
            AutoCompleteStringCollection dspx = new AutoCompleteStringCollection();

            DataTable dtpxl = clpx.getdata(1, 0);
            if (dtpxl != null && dtpxl.Rows.Count > 0)
            {
                foreach (DataRow row in dtpxl.Rows)
                {
                    dspx.Add(row["ten"].ToString());
                }
            }

            cbbPhuong.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            cbbPhuong.AutoCompleteSource = AutoCompleteSource.CustomSource;
            cbbPhuong.AutoCompleteCustomSource = dspx;
            //end gợi ý phường xã

            //khoi tao
            clTaoTbSuDung d = new clTaoTbSuDung();
            DataTable listtb = d.listTableSudung();
            string st = d.full(listtb);
            string[] arr_st = st.Split('-');
            tbSudungold = "tbSudung" + arr_st[1].ToString();
            if (arr_st[0].ToString() == "1")
            {
                int new_month = 0;
                int new_year = 0;
                int current_year = 0;
                int current_month = 0;
                string str_current_year = "";
                string str_current_month = "";
                str_current_year = arr_st[1].Substring(0, 4);
                str_current_month = arr_st[1].Substring(4, arr_st[1].Length - 4);
                current_year = int.Parse(str_current_year);
                current_month = int.Parse(str_current_month);
                if (current_month < 12)
                {
                    new_month = current_month + 1;
                    if (new_month<10)
                        tbSudungnew = "tbSudung" + current_year + "0" + new_month + "";
                    else
                        tbSudungnew = "tbSudung" + current_year + "" + new_month + "";
                   
                }
                else
                {
                    new_year = current_year + 1;
                    new_month = 1;
                    tbSudungnew = "tbSudung" + new_year + "0" + new_month + "";

                }
                try
                {
                    using (sqlcon = clHoadon.GetConnection())
                    {
                        sqlcon.Open();
                        sqlcmd = new SqlCommand("hh_insert_tbsudung", sqlcon);
                        sqlcmd.CommandType = CommandType.StoredProcedure;
                        sqlcmd.CommandTimeout = 180;
                        sqlcmd.Parameters.Add("@tbsudungnew", SqlDbType.NVarChar, 30).Value = tbSudungnew;
                        sqlcmd.Parameters.Add("@tbsudungold", SqlDbType.NVarChar, 30).Value = tbSudungold;
                        sqlcmd.ExecuteNonQuery();
                        sqlcon.Close();
                    }
                }
                catch { }
                finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            }
            else
            {
                tbSudungnew = tbSudungold;
            }
            ///
            clDHTong dht = new clDHTong();
            DataTable dtdht = dht.getdata(0);
            binCombo(cbbDHT, dtdht, "tenDHTong", "id", "<Chọn ĐHT>");
            clPX px = new clPX();
            DataTable dtpx = px.getdata(0, 0);
            binCombo(cbbPhuong, dtpx, "ten", "id", "<Chọn Phường>");
            DataTable dt = new DataTable();
            binCombo(cbbPho, dt, "ten", "id", "<Chọn Phố>");
            //pagetotal();

        }

        void pagetotal(string thangnam,Int16 idDHT,Int16 idPh,Int16 idPho)
        {
            //string thangnamload = tbSudungnew;
            //Int16 idDHtong = Int16.Parse(cbbDHT.SelectedValue.ToString());
            //Int16 idPhuongx = Int16.Parse(cbbPhuong.SelectedValue.ToString());
            //Int16 idP = Int16.Parse(cbbPho.SelectedValue.ToString());

            clTaoTbSuDung clsd = new clTaoTbSuDung();
            rows = clsd.countrow(thangnam, idDHT, idPh, idPho);

            pageNumber = rows % pageSize != 0 ? rows / pageSize + 1 : rows / pageSize;
            lbtotalpage.Text = "/" + pageNumber.ToString();
            cbbpage.Items.Clear();
            for (int i = 1; i <= pageNumber; i++)
            {
                cbbpage.Items.Add(i + "");
            }
            //cbbpage.SelectedIndex = 0;
        }

        private void nuberricPagesize_ValueChanged(object sender, EventArgs e)
        {
            //them ngày 14/2
            int thang = int.Parse(nbrThang.Value.ToString());
            int nam = int.Parse(nbrnam.Value.ToString());
            //string thangnamload = "";
            if (thang < 10)
            {
                thangnamload = "tbSudung" + nam + "0" + thang;
            }
            else
            {
                thangnamload = "tbSudung" + nam +""+ thang;
            }
            //end
            //string thangnamload = tbSudungnew;
            Int16 idDHtong = Int16.Parse(cbbDHT.SelectedValue.ToString());
            Int16 idPhuongx = Int16.Parse(cbbPhuong.SelectedValue.ToString());
            Int16 idP = Int16.Parse(cbbPho.SelectedValue.ToString());

            pageSize = Convert.ToInt32(nuberricPagesize.Value);
            pagetotal(thangnamload,idDHtong,idPhuongx,idP);
            pageNumber = int.Parse(cbbpage.Text);

            clTaoTbSuDung clsd = new clTaoTbSuDung();
            DataTable dtsd = clsd.loadDataSuDung(thangnamload, pageNumber, pageSize, idDHtong, idPhuongx, idP);
            datagridTBSuDung.AutoGenerateColumns = false;
            datagridTBSuDung.DataSource = view = dtsd.DefaultView;

            foreach (DataGridViewRow row in datagridTBSuDung.Rows)
            {
                if (Int16.Parse(row.Cells["trangthaiThay"].Value.ToString()) == 1)
                {
                    row.DefaultCellStyle.BackColor = Color.Yellow;
                }
            }

            //disable cell
            foreach (DataGridViewRow row1 in datagridTBSuDung.Rows)
            {
                row1.Cells["maCu"].ReadOnly = true;
                row1.Cells["tenKH"].ReadOnly = true;
                row1.Cells["socu2"].ReadOnly = true;
                row1.Cells["truockm"].ReadOnly = true;
                row1.Cells["saukm"].ReadOnly = true;
                row1.Cells["thue"].ReadOnly = true;
                row1.Cells["phint"].ReadOnly = true;
                row1.Cells["tongtien"].ReadOnly = true;
                row1.Cells["bangchu"].ReadOnly = true;
            }
        }

        private void btnhienthi_Click(object sender, EventArgs e)
        {
            //them ngày 14/2
            int thangload = int.Parse(nbrThang.Value.ToString());
            int namload = int.Parse(nbrnam.Value.ToString());
            //string thangnamload = "";
            if (thangload < 10)
            {
                thangnamload = "tbSudung" + namload + "0" + thangload;
            }
            else
            {
                thangnamload = "tbSudung" + namload + "" + thangload;
            }
            //end
            //string thangnamload = tbSudungnew;
            cbbpage.Text = "1";
            //string thangnamload = dateTimeThangnam.Value.ToString("MM/dd/yyyy");
            int nam = int.Parse(thangnamload.Substring(8, 4));
            int thang = int.Parse(thangnamload.Substring(12, thangnamload.Length-12));
            lb_thangsudung.Text = "Đang nhập số đồng hồ cho tháng " + thang + " năm " + nam + "";

            Int16 idDHtong = Int16.Parse(cbbDHT.SelectedValue.ToString());
            Int16 idPhuongx = Int16.Parse(cbbPhuong.SelectedValue.ToString());
            Int16 idP = Int16.Parse(cbbPho.SelectedValue.ToString());

            clTaoTbSuDung clsd = new clTaoTbSuDung();
            //rows = clsd.countrow(thangnamload,idDHtong,idPhuongx,idP);

            pagetotal(thangnamload, idDHtong, idPhuongx, idP);

            pageNumber = int.Parse(cbbpage.Text);
            pageSize = int.Parse(nuberricPagesize.Value.ToString());

            DataTable dtsd = clsd.loadDataSuDung(thangnamload, pageNumber, pageSize,idDHtong,idPhuongx,idP);
            datagridTBSuDung.AutoGenerateColumns = false;
            datagridTBSuDung.DataSource = view = dtsd.DefaultView;
            datagridTBSuDung.ReadOnly = false;

            //set color cho rows là khách hàng thay đồng hồ
            foreach (DataGridViewRow row in datagridTBSuDung.Rows)
            {
                if (Int16.Parse(row.Cells["trangthaiThay"].Value.ToString()) == 1)
                {
                    row.DefaultCellStyle.BackColor = Color.Yellow;
                }
            }
            //pagetotal();
            //readonly all columns without columns[1],column[2]
            //for (int i = 0; i < int.Parse(datagridTBSuDung.RowCount.ToString()); i++)
            //{

            //    if (datagridTBSuDung.Rows[i].Cells["socu2"].Value.ToString() == "")
            //        datagridTBSuDung.Rows[i].Cells["socu2"].ReadOnly = false;
            //    else
            //        datagridTBSuDung.Rows[i].Cells["socu2"].ReadOnly = true;
            //}
            //disable cell
            foreach (DataGridViewRow row1 in datagridTBSuDung.Rows)
            {
                row1.Cells["maCu"].ReadOnly = true;
                row1.Cells["tenKH"].ReadOnly = true;
                row1.Cells["socu2"].ReadOnly = true;
                row1.Cells["truockm"].ReadOnly = true;
                row1.Cells["saukm"].ReadOnly = true;
                row1.Cells["thue"].ReadOnly = true;
                row1.Cells["phint"].ReadOnly = true;
                row1.Cells["tongtien"].ReadOnly = true;
                row1.Cells["bangchu"].ReadOnly = true;
            }
        }

        private void cbbpage_SelectedIndexChanged_1(object sender, EventArgs e)
        {
            //them ngày 14/2
            int thangload = int.Parse(nbrThang.Value.ToString());
            int namload = int.Parse(nbrnam.Value.ToString());
            //string thangnamload = "";
            if (thangload < 10)
            {
                thangnamload = "tbSudung" + namload + "0" + thangload;
            }
            else
            {
                thangnamload = "tbSudung" + namload + "" + thangload;
            }
            //end
            //string thangload = dateTimeThangnam.Value.ToString("MM/dd/yyyy");
            //string thangnamload = tbSudungnew;
            pageNumber = int.Parse(cbbpage.Text);
            pageSize = int.Parse(nuberricPagesize.Value.ToString());

            Int16 idDHtong = Int16.Parse(cbbDHT.SelectedValue.ToString());
            Int16 idPhuongx = Int16.Parse(cbbPhuong.SelectedValue.ToString());
            Int16 idP = Int16.Parse(cbbPho.SelectedValue.ToString());

            //currentPageIndex = Convert.ToInt32(cbbpage.Text);
            //fistRow = pageSize * (currentPageIndex - 1); //Dong dau
            //lastRow = pageSize * (currentPageIndex);//Dong cuoi cua 1 trang duoc chon.

            
            clTaoTbSuDung clsd = new clTaoTbSuDung();
            DataTable dtsd = clsd.loadDataSuDung(thangnamload, pageNumber, pageSize, idDHtong, idPhuongx, idP);
            datagridTBSuDung.AutoGenerateColumns = false;
            datagridTBSuDung.DataSource = view = dtsd.DefaultView;

            foreach (DataGridViewRow row in datagridTBSuDung.Rows)
            {
                if (Int16.Parse(row.Cells["trangthaiThay"].Value.ToString()) == 1)
                {
                    row.DefaultCellStyle.BackColor = Color.Yellow;
                }
            }

            //disable cell
            foreach (DataGridViewRow row1 in datagridTBSuDung.Rows)
            {
                row1.Cells["maCu"].ReadOnly = true;
                row1.Cells["tenKH"].ReadOnly = true;
                row1.Cells["socu2"].ReadOnly = true;
                row1.Cells["truockm"].ReadOnly = true;
                row1.Cells["saukm"].ReadOnly = true;
                row1.Cells["thue"].ReadOnly = true;
                row1.Cells["phint"].ReadOnly = true;
                row1.Cells["tongtien"].ReadOnly = true;
                row1.Cells["bangchu"].ReadOnly = true;
            }
        }

        private void cbbPhuong_SelectedIndexChanged_1(object sender, EventArgs e)
        {
            try
            {
                Int16 pxid = Int16.Parse((cbbPhuong.SelectedValue == null) ? "0" : cbbPhuong.SelectedValue.ToString().Trim());
                if (pxid > 0)
                {
                    clPho p = new clPho();
                    AutoCompleteStringCollection dsp = new AutoCompleteStringCollection();
                    DataTable dtp = p.getdata(1, pxid);
                    binCombo(cbbPho, dtp, "ten", "id", "<Chọn Phố>");

                    if (dtp != null && dtp.Rows.Count > 0)
                    {
                        foreach (DataRow row in dtp.Rows)
                        {
                            dsp.Add(row["ten"].ToString());
                        }
                    }

                    cbbPho.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
                    cbbPho.AutoCompleteSource = AutoCompleteSource.CustomSource;
                    cbbPho.AutoCompleteCustomSource = dsp;
                }
            }
            catch { }
        }

        private void btnNhapTheoMaKH_Click(object sender, EventArgs e)
        {
            NhapSoDHTheoMaKH nhaptm = new NhapSoDHTheoMaKH(datagridTBSuDung);
            nhaptm.ShowDialog();

            //them ngày 14/2
            int thangload = int.Parse(nbrThang.Value.ToString());
            int namload = int.Parse(nbrnam.Value.ToString());
            //string thangnamload = "";
            if (thangload < 10)
            {
                thangnamload = "tbSudung" + namload + "0" + thangload;
            }
            else
            {
                thangnamload = "tbSudung" + namload + "" + thangload;
            }
            //end
            //string thangload = dateTimeThangnam.Value.ToString("MM/dd/yyyy");
            //string thangnamload = tbSudungnew;
            pageNumber = int.Parse(cbbpage.Text);
            pageSize = int.Parse(nuberricPagesize.Value.ToString());

            Int16 idDHtong = Int16.Parse(cbbDHT.SelectedValue.ToString());
            Int16 idPhuongx = Int16.Parse(cbbPhuong.SelectedValue.ToString());
            Int16 idP = Int16.Parse(cbbPho.SelectedValue.ToString());

            //currentPageIndex = Convert.ToInt32(cbbpage.Text);
            //fistRow = pageSize * (currentPageIndex - 1); //Dong dau
            //lastRow = pageSize * (currentPageIndex);//Dong cuoi cua 1 trang duoc chon.


            clTaoTbSuDung clsd = new clTaoTbSuDung();
            DataTable dtsd = clsd.loadDataSuDung(thangnamload, pageNumber, pageSize, idDHtong, idPhuongx, idP);
            datagridTBSuDung.AutoGenerateColumns = false;
            datagridTBSuDung.DataSource = view = dtsd.DefaultView;

            foreach (DataGridViewRow row in datagridTBSuDung.Rows)
            {
                if (Int16.Parse(row.Cells["trangthaiThay"].Value.ToString()) == 1)
                {
                    row.DefaultCellStyle.BackColor = Color.Yellow;
                }
            }

            //disable cell
            foreach (DataGridViewRow row1 in datagridTBSuDung.Rows)
            {
                row1.Cells["maCu"].ReadOnly = true;
                row1.Cells["tenKH"].ReadOnly = true;
                row1.Cells["socu2"].ReadOnly = true;
                row1.Cells["truockm"].ReadOnly = true;
                row1.Cells["saukm"].ReadOnly = true;
                row1.Cells["thue"].ReadOnly = true;
                row1.Cells["phint"].ReadOnly = true;
                row1.Cells["tongtien"].ReadOnly = true;
                row1.Cells["bangchu"].ReadOnly = true;
            }
        }

        private void datagridTBSuDung_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            //them ngày 14/2
            int thangload = int.Parse(nbrThang.Value.ToString());
            int namload = int.Parse(nbrnam.Value.ToString());
            //string thangnamload = "";
            if (thangload < 10)
            {
                thangnamload = "tbSudung" + namload + "0" + thangload;
            }
            else
            {
                thangnamload = "tbSudung" + namload + "" + thangload;
            }
            //end
            if (datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["socu2"].Value.ToString() == "")
            {
                datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["somoi2"].Value = 0;
                datagridTBSuDung.CurrentCell = datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString()) - 2].Cells["socu2"];
                return;

            }
            try
            {
                using (sqlcon = clHoadon.GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("hh_st_tinhcuoc", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@idkh", SqlDbType.BigInt).Value = datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["idkh"].Value.ToString();
                    sqlcmd.Parameters.Add("@somoi2", SqlDbType.SmallInt).Value = datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["somoi2"].Value.ToString();
                    sqlcmd.Parameters.Add("@tbSudung", SqlDbType.NVarChar).Value = thangnamload;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }

            //Show
            try
            {
                using (sqlcon =clHoadon.GetConnection())
                {
                    sqlcon.Open();
                    SqlCommand command = new SqlCommand("select * from "+thangnamload+" where idkh=" + datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["idkh"].Value.ToString() + "", sqlcon);
                    SqlDataReader reader = command.ExecuteReader();
                    //datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["tongtien"].Value = int.Parse(datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["idkh"].Value.ToString());
                    while (reader.Read())
                    {
                        datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["truockm"].Value = int.Parse(reader["truockm"].ToString());
                        datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["saukm"].Value = int.Parse(reader["saukm"].ToString());

                        datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["tongtien"].Value = int.Parse((reader["tongtien"].ToString() == "") ? "0" : reader["tongtien"].ToString());
                        datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["thue"].Value = int.Parse((reader["thue"].ToString() == "") ? "0" : reader["thue"].ToString());

                        datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["phint"].Value = int.Parse((reader["phint"].ToString() == "") ? "0" : reader["phint"].ToString());
                        datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["bangchu"].Value = reader["bangchu"].ToString();
                    }

                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            
        }

        private void btnsuacsc_Click(object sender, EventArgs e)
        {
            SuaChiSoCu scsc = new SuaChiSoCu();
            scsc.ShowDialog();

            //them ngày 14/2
            int thangload = int.Parse(nbrThang.Value.ToString());
            int namload = int.Parse(nbrnam.Value.ToString());
            //string thangnamload = "";
            if (thangload < 10)
            {
                thangnamload = "tbSudung" + namload + "0" + thangload;
            }
            else
            {
                thangnamload = "tbSudung" + namload + "" + thangload;
            }
            //end
            //string thangload = dateTimeThangnam.Value.ToString("MM/dd/yyyy");
            //string thangnamload = tbSudungnew;
            pageNumber = int.Parse(cbbpage.Text);
            pageSize = int.Parse(nuberricPagesize.Value.ToString());

            Int16 idDHtong = Int16.Parse(cbbDHT.SelectedValue.ToString());
            Int16 idPhuongx = Int16.Parse(cbbPhuong.SelectedValue.ToString());
            Int16 idP = Int16.Parse(cbbPho.SelectedValue.ToString());

            //currentPageIndex = Convert.ToInt32(cbbpage.Text);
            //fistRow = pageSize * (currentPageIndex - 1); //Dong dau
            //lastRow = pageSize * (currentPageIndex);//Dong cuoi cua 1 trang duoc chon.


            clTaoTbSuDung clsd = new clTaoTbSuDung();
            DataTable dtsd = clsd.loadDataSuDung(thangnamload, pageNumber, pageSize, idDHtong, idPhuongx, idP);
            datagridTBSuDung.AutoGenerateColumns = false;
            datagridTBSuDung.DataSource = view = dtsd.DefaultView;

            foreach (DataGridViewRow row in datagridTBSuDung.Rows)
            {
                if (Int16.Parse(row.Cells["trangthaiThay"].Value.ToString()) == 1)
                {
                    row.DefaultCellStyle.BackColor = Color.Yellow;
                }
            }

            //disable cell
            foreach (DataGridViewRow row1 in datagridTBSuDung.Rows)
            {
                row1.Cells["maCu"].ReadOnly = true;
                row1.Cells["tenKH"].ReadOnly = true;
                row1.Cells["socu2"].ReadOnly = true;
                row1.Cells["truockm"].ReadOnly = true;
                row1.Cells["saukm"].ReadOnly = true;
                row1.Cells["thue"].ReadOnly = true;
                row1.Cells["phint"].ReadOnly = true;
                row1.Cells["tongtien"].ReadOnly = true;
                row1.Cells["bangchu"].ReadOnly = true;
            }
        }

        private void datagridTBSuDung_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            Int64 idkh1 = Int64.Parse(datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["idkh"].Value.ToString());
            int i;
            try
            {
                using (sqlcon = clHoadon.GetConnection())
                {
                    sqlcon.Open();
                    SqlCommand command = new SqlCommand("select id,maCu,idMDSD,sokhauSD from tbKhachHang where trangthai=3 and id="+idkh1+"", sqlcon);
                    SqlDataAdapter reader = new SqlDataAdapter(command);
                    DataTable dt = new DataTable();
                    reader.Fill(dt);

                    for (i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        if ((Int16.Parse(dr["idMDSD"].ToString()) == 5) || (Int16.Parse(dr["idMDSD"].ToString()) == 6))
                        {
                            NhapSoKhau nhapsokhau = new NhapSoKhau();
                            nhapsokhau.txtMaKH.Text = dr["maCu"].ToString();
                            nhapsokhau.txtsokhau.Text = dr["soKhauSD"].ToString();
                            nhapsokhau.ShowDialog();
                        }
                    }

                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcon.Dispose(); }
        }

        private void btntinh1gia_Click(object sender, EventArgs e)
        {
            //them ngày 14/2
            int thangload = int.Parse(nbrThang.Value.ToString());
            int namload = int.Parse(nbrnam.Value.ToString());
            //string thangnamload = "";
            if (thangload < 10)
            {
                thangnamload = "tbSudung" + namload + "0" + thangload;
            }
            else
            {
                thangnamload = "tbSudung" + namload + "" + thangload;
            }
            //end
            try
            {
                using (sqlcon = clHoadon.GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_tinhcuoc_1gia", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@idkh", SqlDbType.BigInt).Value = datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["idkh"].Value.ToString();
                    sqlcmd.Parameters.Add("@somoi2", SqlDbType.SmallInt).Value = datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["somoi2"].Value.ToString();
                    sqlcmd.Parameters.Add("@tbSudung", SqlDbType.NVarChar).Value = thangnamload;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            //Show
            try
            {
                using (sqlcon = clHoadon.GetConnection())
                {
                    sqlcon.Open();
                    SqlCommand command = new SqlCommand("select * from " + thangnamload + " where idkh=" + datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["idkh"].Value.ToString() + "", sqlcon);
                    SqlDataReader reader = command.ExecuteReader();
                    //datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["tongtien"].Value = int.Parse(datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["idkh"].Value.ToString());
                    while (reader.Read())
                    {
                        datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["truockm"].Value = int.Parse(reader["truockm"].ToString());
                        datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["saukm"].Value = int.Parse(reader["saukm"].ToString());

                        datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["tongtien"].Value = int.Parse(reader["tongtien"].ToString());
                        datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["thue"].Value = int.Parse(reader["thue"].ToString());
                        //datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["truockm"].Value = int.Parse(reader["truockm"].ToString());
                        //datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["saukm"].Value = int.Parse(reader["saukm"].ToString());
                        datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["phint"].Value = int.Parse(reader["phint"].ToString());
                        datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["bangchu"].Value = reader["bangchu"].ToString();
                    }

                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //them ngày 14/2
            int thangload = int.Parse(nbrThang.Value.ToString());
            int namload = int.Parse(nbrnam.Value.ToString());
            //string thangnamload = "";
            if (thangload < 10)
            {
                thangnamload = "tbSudung" + namload + "0" + thangload;
            }
            else
            {
                thangnamload = "tbSudung" + namload + "" + thangload;
            }
            //end
            Int64 idkh = Int64.Parse(datagridTBSuDung.Rows[int.Parse(datagridTBSuDung.CurrentRow.Index.ToString())].Cells["idkh"].Value.ToString());
            int i;
            try
            {
                using (sqlcon = clHoadon.GetConnection())
                {
                    sqlcon.Open();
                    SqlCommand command = new SqlCommand("select tbKhachHang.id,maCu,loaiKH,socu2 from tbKhachHang,"+thangnamload+" where trangthai=3 and tbKhachHang.id=" + idkh + " and "+thangnamload+".idkh=tbKhachHang.id", sqlcon);
                    SqlDataAdapter reader = new SqlDataAdapter(command);
                    DataTable dt = new DataTable();
                    reader.Fill(dt);

                    for (i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        if (Int16.Parse(dr["loaiKH"].ToString()) == 2)
                        {
                            TinhCuocCQLuyThua cqlt = new TinhCuocCQLuyThua();
                            cqlt.txtidkh.Text = dr["id"].ToString();
                            cqlt.txtMaKH.Text = dr["maCu"].ToString();
                            cqlt.txtChisocu.Text = dr["socu2"].ToString();
                            cqlt.ShowDialog();
                        }
                        else
                        {
                            MessageBox.Show("Khách hàng này không thuộc loại khách hàng cơ quan.");
                        }
                    }
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcon.Dispose(); }
        }
    }
}
